Back to Main Menu

Bulk Create/Update Spatial Data

Objective: Gain an understanding of how to Bulk Create/Update Spatial Data.

 

Introduction

The Data Exchange Wizard is used to bulk import spatial data.

 

Since there are a large number of spatial data formats and co-ordinate systems in use, the spatial data must be prepared in a particular format and co-ordinate system prior to import.

 

The Data Exchange import process itself is no different from the process for other modules supported by Data Exchange. The "Module" is 'Mapping' and the "Category" is 'Asset Spatial'. Refer to article Data Exchange Import Wizard for more detail on how to import data.

 

Spatial Data Preparation

There are 3 primary requirements for preparing the spatial data for loading via Data Exchange:

 

The spatial data co-ordinates are in decimal degrees as latitude/longitude with the WGS84 co-ordinate system. The Spatial Reference System Identifier (SRID) for this is 4326 (also known as EPSG:4326)

 

The format of the spatial data is WKT (Well Known Text)

 

Polygon orientation is important if not also providing the centre point as part of the upload. The outer ring vertices must be defined in an anti-clockwise order to allow the centre point to be calculated by Brightly Assetic as part of the upload.

 

NOTE  The polygon centroid should be included in the 'Point' field when loading polygons to avoid potential issues that can occur if the polygon orientation is invalid.

 

Data Transformations

The spatial data in the GIS will typically be in a projected co-ordinate system rather than a geodetic (latitude/longitude) co-ordinate system. It will therefore require transformation to the projection required for Data Exchange. Depending on the GIS tools available this may be a straight forward process or involve some investigation into the capabilities of your GIS.

ogr2ogr

The tool ogr2ogr may be used to transform spatial data to the correct projection and also output as WKT.

 

This tool is available for download via the site http://www.gisinternals.com/release.php. It is bundled with 'MapServer', so download the latest version. There is no need to install MapServer or ogr2ogr, the download can be unzipped to a folder and the ogr2ogr.exe executable run from that folder via a command window (or batch file).

 

The following is a sample export from an SQL Server table with geometry column to a WKT csv file export. The database server is "Myserver", the database instance is "MyGISDB" and the spatial table is "Roads".

 

The source projection (projection of the Roads data) is EPSG:28355 which equates to GDA Zone 55. It is to be converted by ogr2ogr to EPSG:4326, the projection required for upload to Assetic.

 

ogr2ogr -f "CSV" -s_srs "EPSG:28355" -t_srs "EPSG:4326" "c:\temp\roads.csv"

"MSSQL:server=MyServer;database=MyGISDB;tables=Roads(SP_GEOMETRY)

;trusted_connection=yes;" -sql "select AssetID, SP_GEOMETRY from dbo.Roads"

-lco "GEOMETRY=AS_WKT" -lco "GEOMETRY_NAME=SP_GEOMETRY"

 

Sample Batch File WKT export

The following batch file may be used to create an import file from an ESRI shp file.

 

Click on this the following link to download: ogr2ogrShp2WKT.bat

 

The following changes may need to be applied to the batch file:

Lines 8 & 9: Make sure the folder locations of the OGR/QGIS tools are correct

Line 12: Define the folder that the generated csv file will be written to

Line 13: Define the name of the generated csv file to be used for the Data Exchange import

Line 16: Define the file location and file name of the shapefile to process

Line 17: Define the attribute field name in the source GIS data that holds the Assetic Asset Id

Line 18: Define the map projection of the source GIS data using the EPSG code. In the provided example it is MGA54

 

echo off

rem Create WKT import file from shp file with polygons

rem Will reproject/transform to EPSG:4326 - Geography data

rem Will get polygon centroid and include in export

rem Uses ogr2ogr and SQLite (https://www.gdal.org/ogr_sql_sqlite.html)

rem make sure GDAL library is set and path to ogr2ogr

set GDAL_DATA=C:\Program Files\QGIS 2.18\share\gdal

set OGR_PATH=C:\Program Files\QGIS 2.18\bin

rem directory and filename to write data exchange import file to. Must exist.

set output_dir=c:\temp\wkt_files

set output_wkt_file=playground_import.csv

rem set the following source information. SRS are the EPSG codes for projection

set input_shp_file=C:\Projects\ShapeFiles\Playground_Area.shp

set asset_id_field=asset_id

set input_srs=28354

rem output projection needs to be 4326

set output_srs=4326

rem build some variables

for %%f in (%input_shp_file%) do set input_table=%%~nf

set pwd=%cd%

echo on

rem step 1. change directory to the output directory

cd %output_dir%

rem step 2. create temp file with polygon centroid as a string, wont be reprojected

"%OGR_PATH%\ogr2ogr.exe" "tmp.csv" -s_srs EPSG:%input_srs% -t_srs EPSG:%output_srs% -sql "SELECT geometry, ST_AsText(ST_Centroid(geometry)) as point_str, %asset_id_field% FROM %input_table%" -dialect sqlite -f CSV %input_shp_file% -lco GEOMETRY=AS_WKT

rem step 3. read temp file and reproject centroid, and set column names

"%OGR_PATH%\ogr2ogr.exe" %output_wkt_file% -s_srs EPSG:%input_srs% -t_srs EPSG:%output_srs% -sql "select ST_PointFromText(point_str, %input_srs%), ST_AsText(geometry) as Polygon, %asset_id_field% as [Complex Asset Id] FROM tmp" -dialect sqlite -f CSV "tmp.csv" -lco GEOMETRY=AS_WKT -lco GEOMETRY_NAME=Point

rem step 4. remove temp file

del tmp.csv

rem strp 5. change directory back to starting directory

cd %pwd%

pause

 

WKT export

Once the data is in the correct projection, it needs to be extracted from the GIS in WKT format.

This can be achieved via the tools available in your GIS, or if it is not capable, the ogr2ogr tool mentioned above may be used.

Example SQL Server Query to get spatial data in WKT format:

select SP_GEOMETRY.STAsText() from Buildings

In the above example it is assumed the spatial data is in a column of type geography. If it is in a table of type geometry it must first be transformed to geography using your GIS tools, or ogr2ogr.

 

Supported WKT types are: POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON

 

Import File Format

The import file is saved in CSV format and includes a header row that identifies each column.

 

The columns are:

Header Description Mandatory

Complex Asset Id Asset ID. Must be unique, and the asset must exist. Yes

Point

WKT defining for a 'POINT' or 'MULTIPOINT'.

If undefined:

The centroid/mid point of the line/polygon is automatically calculated and applied to the 'Point' definition of the asset.

If polygon:

the polygon outer ring vertices must be defined in an anti-clockwise order, otherwise the record will be rejected because the centroid cannot be calculated. This potential issue can be avoided by also defining the polygon centroid in this 'Point' field.

No.

Either a Polygon or Line is required if there is no point defined

Polygon WKT defining a 'POLYGON','MULTIPOLYGON'. No. Point or Line required

Line WKT defining a 'LINESTRING','MULTILINESTRING' No. Point or Polygon required